package com.wq.httpclientcore.service;

import com.wq.httpclientcore.config.DynamicDataSourceConfig;
import com.wq.httpclientcore.entities.po.DbConnectionConfig;
import com.wq.httpclientcore.entities.po.DbQueryConfig;
import com.wq.httpclientcore.mapper.DbConnectionConfigMapper;
import com.wq.httpclientcore.mapper.DbQueryConfigMapper;
import lombok.RequiredArgsConstructor;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Service
@RequiredArgsConstructor
public class DynamicQueryService {

    private final DynamicDataSourceConfig dataSourceConfig;
    private final DbConnectionConfigMapper connectionConfigMapper;
    private final DbQueryConfigMapper queryConfigMapper;

    /**
     * 执行动态查询
     */
    public List<Map<String, Object>> executeQuery(Long queryConfigId, Integer pageSize, Integer pageNo) {
        // 1. 获取查询配置
        DbQueryConfig queryConfig = queryConfigMapper.selectById(queryConfigId);
        if (queryConfig == null) {
            throw new RuntimeException("未找到查询配置");
        }

        // 2. 获取数据库连接配置
        DbConnectionConfig connectionConfig = connectionConfigMapper.selectById(queryConfig.getDbConnectionId());
        if (connectionConfig == null || !connectionConfig.getEnabled()) {
            throw new RuntimeException("数据库连接无效或已禁用");
        }

        // 3. 获取数据源并创建 JdbcTemplate
        DataSource dataSource = dataSourceConfig.getDataSource(connectionConfig);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        // 4. 处理分页参数
        String finalSql = applyPagination(queryConfig.getQuerySql(), connectionConfig.getDbType(), pageSize, pageNo);

        // 5. 检查 SQL 语句类型，只允许执行 SELECT 语句
        if (!isSelectStatement(finalSql)) {
            throw new RuntimeException("只允许执行 SELECT 语句");
        }

        // 6. 执行查询
        return jdbcTemplate.queryForList(finalSql);
    }
    /**
     * 检查 SQL 语句是否为 SELECT 语句
     */
    private boolean isSelectStatement(String sql) {
        // 去除 SQL 语句中的注释
        sql = removeComments(sql);
        // 去除 SQL 语句前后的空白字符
        sql = sql.trim();

        // 处理以 WITH 开头的 CTE 查询
        if (sql.toLowerCase().startsWith("with")) {
            // 找到第一个 SELECT 关键字
            int selectIndex = sql.toLowerCase().indexOf("select");
            if (selectIndex != -1) {
                // 截取 SELECT 之后的部分继续检查危险关键字
                sql = sql.substring(selectIndex);
            } else {
                return false;
            }
        }

        // 忽略大小写，检查是否以 SELECT 开头
        if (!sql.toLowerCase().startsWith("select")) {
            return false;
        }
        // 进一步检查是否包含危险关键字（如 INSERT、UPDATE、DELETE 等）
        return!containsDangerousKeywords(sql);
    }

    /**
     * 去除 SQL 语句中的注释
     */
    private String removeComments(String sql) {
        // 去除单行注释
        sql = sql.replaceAll("--.*", "");
        // 去除多行注释
        sql = sql.replaceAll("/\\*.*?\\*/", "");
        return sql;
    }

    /**
     * 检查 SQL 语句是否包含危险关键字
     */
    private boolean containsDangerousKeywords(String sql) {
        String[] dangerousKeywords = {"insert", "update", "delete", "drop", "alter", "truncate"};
        Pattern pattern = Pattern.compile("\\b(" + String.join("|", dangerousKeywords) + ")\\b", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(sql);
        return matcher.find();
    }

    /**
     * 根据数据库类型添加分页语法
     */
    private String applyPagination(String originalSql, String dbType, Integer pageSize, Integer pageNo) {
        if (pageSize == null || pageNo == null) return originalSql;

        return switch (dbType.toLowerCase()) {
            case "mysql" -> String.format("%s LIMIT %d OFFSET %d", originalSql, pageSize, (pageNo - 1) * pageSize);
            case "postgresql" -> String.format("%s LIMIT %d OFFSET %d", originalSql, pageSize, (pageNo - 1) * pageSize);
            default -> throw new IllegalArgumentException("不支持的分页数据库：" + dbType);
        };
    }
}